package org.cuatrovientos.sqlite.premierLeague;

import java.sql.*;

/**
 * Shows premier league table, which is stored in a SQLite DB.
 * @author The Special One
 *
 */
public class PremierLeague {
	Connection conection;

	/**
	 * default constructor
	 */
	public PremierLeague () {
	  
	}
  
	/**
	 * shows table data from database
	 * @return String with data
	 */
	public String showLeagueTable () {
		String resultado = "";
		 try {
			 	// PART 1 connection
			 	// First of all we connect to DB
			    conexionBD();
			
			    // PART 2 select data
			    // We create a new Statement
			    resultado = selectData(resultado);
			    
		// In case of exception we catch them
		  } catch (SQLException sqle) {
			  System.err.println("SQL Exception: " + sqle.getMessage());
		  } catch (Exception e) {
			  System.err.println("Exception: " + e.getMessage());
		  }
		 
		 return resultado;
	}

	private String selectData(String resultado) throws SQLException {
		Statement statement = conection.createStatement();

		// Now we select data and we show results in a loop.
		ResultSet miResulSet = statement.executeQuery("select * from premiertable;");
		while (miResulSet.next()) {
		  resultado += miResulSet.getInt("id");
		  resultado += ".- " + miResulSet.getString("name");
		  resultado += "\tpoints: " + miResulSet.getInt("points") + "\n";
		}
		
		// We close both the ResultSet and the DB connection
		miResulSet.close();
		conection.close();
		return resultado;
	}

	private void conexionBD() throws ClassNotFoundException, SQLException {
		Class.forName("org.sqlite.JDBC");
		conection = DriverManager.getConnection("jdbc:sqlite:premierleague.db");
	}
  
  public static void main(String[] args) {
	  PremierLeague miPremier = new PremierLeague();
	  System.out.println(miPremier.showLeagueTable());
	 
  }
}